package com.example.project.dao;

import com.example.project.pojo.Car;
import com.example.project.pojo.User;
import com.example.project.util.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CarDAO {
    //添加车辆
    public void addCar(String car, Integer userid) {
        Connection connection = DBHelper.getConnection();
        PreparedStatement ps = null;
        String sql = "insert into car(license,user_id) values(?,?)";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, car);
            ps.setInt(2, userid);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.close(ps, connection);
        }
    }

    //通过用户id查找车辆
    public List<Car> findCarByuserID(Integer userId) {
        Connection connection = DBHelper.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<Car> carList = new ArrayList<>();
        String sql = "select * from car where user_id=?";
        try {

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, userId);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Car car = new Car();
                User user = new User();
                car.setUserId(rs.getInt("user_id"));
                user.setId(rs.getInt("user_id"));
                car.setId(rs.getInt("id"));
                car.setLicense(rs.getString("license"));
                car.setUser(user);

                carList.add(car);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close(rs, preparedStatement, connection);
        }
        return carList;
    }


    //删除车辆
    public void deleteById(int id) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        connection = DBHelper.getConnection();

        String sql = "delete from car where id=?";

        try {
            ps = connection.prepareStatement(sql);

            ps.setInt(1, id);

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(resultSet, ps, connection);
        }

    }

    public Car findCarByID(Integer id) {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Car car = new Car();
        String sql = "select * from car where id =?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            car.setId(id);
            rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("user_id"));
                car.setUser(user);
                car.setLicense(rs.getString("license"));
                car.setId(id);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close(rs, ps, conn);
        }
        return car;
    }

    //修改车辆信息
    public void updateCarLicense(Car car) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection = DBHelper.getConnection();

        String sql = "update car set license=? where id=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, car.getLicense());
            preparedStatement.setInt(2, car.getId());

            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.close(resultSet, preparedStatement, connection);
        }
    }

    public boolean CheckLicenseBeforeAdd(String license) {
        Connection connection = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "select * from car where license=?";

        try {
            ps = connection.prepareStatement(sql);

            ps.setString(1, license);

            rs = ps.executeQuery();

            boolean flag = rs.next();

            return !flag;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close(rs, ps, connection);
        }
        return false;
    }
    public void deleteCarByLicense(String id)
    {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet  resultSet = null;

//        System.out.println(license);
        //链接数据库
        connection = DBHelper.getConnection();

        String sql="delete from car where license=?";

        try {
            //往数据库中间添加数据 preparedStatement是载体
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1,id);
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(resultSet,preparedStatement,connection);
        }


    }
    //登录manage查找所有车辆
    public List<Car> findCarById()
    {

        List<Car> carList = new ArrayList<>();

        //MySQL-->carList
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet  resultSet = null;

        //链接数据库
        connection = DBHelper.getConnection();

        String sql = "SELECT*FROM car,user WHERE car.user_id=user.id";

        try {
            //往数据库中间添加数据 preparedStatement是载体
            preparedStatement = connection.prepareStatement(sql);
            //结果集
            resultSet = preparedStatement.executeQuery();
            // preparedStatement.executeUpdate();

            //resultSet-->car-->carList
            //移动指针
            while (resultSet.next())
            {
                //resultSet-->car
                Car car = new Car();
                car.setId(resultSet.getInt("car.id"));

                car.setUserId(resultSet.getInt("user_id"));

                car.setLicense(resultSet.getString("car.license"));

                //resultSet-->user
                User user =new User();
                user.setId(resultSet.getInt("user.id"));
                user.setUserName(resultSet.getString("user.username"));
                //user-->car
                car.setUser(user);

                //car-->carList
                carList.add(car);

            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(resultSet,preparedStatement,connection);
        }

        return carList;
    }

}
















